package com.swe.chums.dao.impl;

import com.swe.chums.dao.UserDao;
import com.swe.chums.dao.model.User;
import com.swe.chums.dao.model.UserPk;
import com.swe.chums.dao.exceptions.UserDaoException;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Transactional;

public class UserDaoImpl extends AbstractDAO implements ParameterizedRowMapper<User>, UserDao
{
	protected SimpleJdbcTemplate jdbcTemplate;

	protected DataSource dataSource;

	/**
	 * Method 'setDataSource'
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return UserPk
	 */
	@Transactional
	public UserPk insert(User dto)
	{
		jdbcTemplate.update("INSERT INTO " + getTableName() + " ( username, name, email, password, user_type_id, register_date, average_rating ) VALUES ( ?, ?, ?, ?, ?, ?, ? )",dto.getUsername(),dto.getName(),dto.getEmail(),dto.getPassword(),dto.getUserTypeId(), dto.getRegisterDate(), dto.getAverageRating());
		UserPk pk = new UserPk();
		pk.setId( jdbcTemplate.queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the user table.
	 */
	@Transactional
	public void update(UserPk pk, User dto) throws UserDaoException
	{
		jdbcTemplate.update("UPDATE " + getTableName() + " SET id = ?, username = ?, name = ?, email = ?, password = ?, user_type_id = ?, register_date=?, average_rating=? WHERE id = ?",dto.getId(),dto.getUsername(),dto.getName(),dto.getEmail(),dto.getPassword(),dto.getUserTypeId(),dto.getRegisterDate(),dto.getAverageRating(),pk.getId());
	}

	/** 
	 * Deletes a single row in the user table.
	 */
	@Transactional
	public void delete(UserPk pk) throws UserDaoException
	{
		jdbcTemplate.update("DELETE FROM " + getTableName() + " WHERE id = ?",pk.getId());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return User
	 */
	public User mapRow(ResultSet rs, int row) throws SQLException
	{
		User dto = new User();
		dto.setId( rs.getInt( 1 ) );
		dto.setUsername( rs.getString( 2 ) );
		dto.setName( rs.getString( 3 ) );
		dto.setEmail( rs.getString( 4 ) );
		dto.setPassword( rs.getString( 5 ) );
		dto.setUserTypeId( rs.getInt( 6 ) );
                dto.setRegisterDate(rs.getTimestamp(7));
                dto.setAverageRating(rs.getDouble(8));
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "spring2013g3.user";
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'id = :id'.
	 */
	@Transactional
	public User findByPrimaryKey(int id) throws UserDaoException
	{
		try {
			List<User> list = jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE id = ?", this,id);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria ''.
	 */
	@Transactional
	public List<User> findAll() throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " ORDER BY id", this);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'user_type_id = :userTypeId'.
	 */
	@Transactional
	public List<User> findByUserType(int userTypeId) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE user_type_id = ?", this,userTypeId);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'id = :id'.
	 */
	@Transactional
	public List<User> findWhereIdEquals(int id) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE id = ? ORDER BY id", this,id);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'username = :username'.
	 */
	@Transactional
	public List<User> findWhereUsernameEquals(String username) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE username = ? ORDER BY username", this,username);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'name = :name'.
	 */
	@Transactional
	public List<User> findWhereNameEquals(String name) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE name = ? ORDER BY name", this,name);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'email = :email'.
	 */
	@Transactional
	public List<User> findWhereEmailEquals(String email) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE email = ? ORDER BY email", this,email);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'password = :password'.
	 */
	@Transactional
	public List<User> findWherePasswordEquals(String password) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE password = ? ORDER BY password", this,password);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the user table that match the criteria 'user_type_id = :userTypeId'.
	 */
	@Transactional
	public List<User> findWhereUserTypeIdEquals(int userTypeId) throws UserDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE user_type_id = ? ORDER BY user_type_id", this,userTypeId);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the user table that matches the specified primary-key value.
	 */
	public User findByPrimaryKey(UserPk pk) throws UserDaoException
	{
		return findByPrimaryKey( pk.getId() );
	}

        @Override
        public List<User> findLatest(int limit) throws UserDaoException {
                try {
			return jdbcTemplate.query("SELECT id, username, name, email, password, user_type_id, register_date, average_rating FROM " + getTableName() + " WHERE user_type_id=1  ORDER BY register_date desc LIMIT ?", this, limit);
		}
		catch (Exception e) {
			throw new UserDaoException("Query failed", e);
		}
            
        }

}
